SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



alter     procedure p_updatemnfworkinguseqty
@relationbid integer,
@relationbno integer,
@relationbdtlno integer,
@productid integer,
@bdate datetime,
@diffqty decimal(18,4)
as
  declare @unitassrate decimal(18,4)  
  select @unitassrate = rate from td_unitprice where productid = @productid and u_no=0
  
  if exists(select m.bid from tb_mnfmaking m
    left join tb_mnfmakingrawmaterials d on m.bid=d.bid
    where d.relationbid=@relationbid  and d.relationbno=@relationbno and d.relationbdtlno=@relationbdtlno
      and productid=@productid and year(m.bdate)>=year(@bdate) and month(m.bdate)>=month(@bdate))
  begin
    if (@unitassrate<>0)
      update tb_mnfmakingrawmaterials set bquantity = bquantity+ @diffqty,quantity = quantity+ @diffqty,
      assqty = (quantity+ @diffqty)/@unitassrate
      where relationbid =@relationbid and relationbno=@relationbno and relationbdtlno=@relationbdtlno
        and productid=@productid and bid in (select m.bid from tb_mnfmaking m
          left join tb_mnfmakingrawmaterials d on(m.bid=d.bid)
      where  d.relationbid=@relationbid and d.relationbno=@relationbno and d.relationbdtlno=@relationbdtlno 
        and d.productid=@productid and year(m.bdate)>=year(@bdate) and month(m.bdate)>=month(@bdate)) 
    else
      update tb_mnfmakingrawmaterials set bquantity = bquantity+ @diffqty,quantity = quantity+ @diffqty, assqty = 0
        where relationbid =@relationbid and relationbno=@relationbno and relationbdtlno=@relationbdtlno
          and productid=@productid and bid in (select m.bid from tb_mnfmaking m
            left join tb_mnfmakingrawmaterials d on(m.bid=d.bid)
            where  d.relationbid=@relationbid and d.relationbno=@relationbno and d.relationbdtlno=@relationbdtlno and d.productid=@productid
      and year(m.bdate)>=year(@bdate) and month(m.bdate)>=month(@bdate))   
  end
	
	




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

